define erp='erp_mli2.';
define outils='outils_mli2.';



--CALL uvse_ExistsOrCreate('PAR_MODPAI');




DELETE FROM  se_PAR_MODPAI ;


--SELECT * FROM uv_cpaiements
--select *  from se_PAR_MODPAI
--select * from &erp.PAR_MODPAI


INSERT INTO se_PAR_MODPAI
 (SELECT ID modpai_cod,
         NULL modpaiaco_cod,
         NULL suppl_cod,
         NULL fam_cod,
         NULL destana_cod,
         0 enreg_id,
         NULL postana_cod,
         NULL modregaco_cod,
         'N' enreg_s,
         libelle modpai_lib,
         NULL modpai_lib2,
         abrege modpaiabr_lib,
         'T' tiers_typ,
         Decode(proforma,'P','O','N') ediproforma_ok,
         Decode(ESCPT/100,0,NULL,ESCPT/100) esc_tx,
         0 eschf_tx,
         'N' contremb_ok,
         'N' facauto_ok
     FROM uv_cpaiements fuv) ;

 UPDATE se_PAR_MODPAI tf
 SET enreg_id = (select enreg_id FROM &erp.PAR_MODPAI f WHERE f.modpai_cod=tf.modpai_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.PAR_MODPAI f WHERE f.modpai_cod=tf.modpai_cod);

 UPDATE se_PAR_MODPAI SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('modpai_cod','PAR_MODPAI','O') ;



 commit;

 select * from (
SELECT &outils.stragg(source) source,modpai_cod, modpaiaco_cod, suppl_cod, fam_cod, destana_cod, enreg_id, postana_cod, modregaco_cod, enreg_s, modpai_lib, modpai_lib2, modpaiabr_lib, tiers_typ, ediproforma_ok, esc_tx, eschf_tx, contremb_ok, facauto_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_PAR_MODPAI g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.PAR_MODPAI g
  )
GROUP BY modpai_cod, modpaiaco_cod, suppl_cod, fam_cod, destana_cod, enreg_id, postana_cod, modregaco_cod, enreg_s, modpai_lib, modpai_lib2, modpaiabr_lib, tiers_typ, ediproforma_ok, esc_tx, eschf_tx, contremb_ok, facauto_ok
HAVING Count(1)!=2) r
ORDER BY modpai_cod;